﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Windows.Forms;
using Npgsql;


namespace Emploees
{
    class Global_variables
    {
        
        static string time_day;
        static string time_month;
        static string time_year;

        static string postgresql_conn = string.Empty;
        static public NpgsqlConnection conn;
        static public int id_department; // идентификатор кафедры
        static public int id_faculty;// идентификатор факультета
        static public int id_user; // идентификатор пользователя
        static public string current_time; // текущее время
        static public string current_date; // текущая дата
        static public string sql = "select  \"Name\",\"Data\",\"StartWork\",\"EndWork\" from \"WorkTime\".\"WorkTimes\", \"WorkTime\".\"Employee\" where \"WorkTime\".\"WorkTimes\".\"id_name\"=@a and \"WorkTime\".\"WorkTimes\".\"id_name\"=\"WorkTime\".\"Employee\".\"id\" and \"Data\"=@b";
        static public string sql1 = "select date_part('hour', current_time)";
        static public string sql2 = "select date_part('minute', current_time)";
        
        static public string sql4 = "select * from \"WorkTime\".\"WorkTimes\" where \"Data\"=@a and id_name=@b";
        static public string sql5 = "select * from \"WorkTime\".\"WorkTimes\" where \"EndWork\" is not null and id_name=@a and \"Data\"=@b";
        static public string sql6 = "select  \"Name\",\"Data\",\"StartWork\",\"EndWork\" from \"WorkTime\".\"WorkTimes\", \"WorkTime\".\"Employee\" where \"WorkTime\".\"WorkTimes\".\"id_name\"=@a and \"WorkTime\".\"WorkTimes\".\"id_name\"=\"WorkTime\".\"Employee\".\"id\" and \"Data\"=@b";
        static public string sql7 = "select date_part('day', current_date)";
        static public string sql8 = "select date_part('month', current_date)";
        static public string sql9 = "select date_part('year', current_date)";
        static public string sql10 = "select * from \"WorkTime\".\"WorkTimes\" where \"WorkTime\".\"WorkTimes\".id_name=@a and \"WorkTime\".\"WorkTimes\".\"Data\"=@b";

        //public static Form passwordform = new PasswordForm();
        //public static Form worktimeform = new WorkTimeForm();

        public static NpgsqlConnection connecttodb()
        {

            
            postgresql_conn = postgresql_conn + "Server=" + ConfigurationManager.AppSettings["Server"] + ";";
            postgresql_conn = postgresql_conn + "Port=" + ConfigurationManager.AppSettings["Port"] + ";";
            postgresql_conn = postgresql_conn + "User Id=" + ConfigurationManager.AppSettings["User Id"] + ";";
            postgresql_conn = postgresql_conn + "Password=" + ConfigurationManager.AppSettings["Password"] + ";";
            postgresql_conn = postgresql_conn + "Database=" + ConfigurationManager.AppSettings["Database"];
            conn = new NpgsqlConnection(postgresql_conn);
            //MessageBox.Show(postgresql_conn);
            return conn;
        }

        public static void CurrentDate()
        {
            WorkTimeForm.conn.Open();
            NpgsqlCommand command1 = new NpgsqlCommand(Global_variables.sql7, WorkTimeForm.conn);
            NpgsqlDataReader dr3 = command1.ExecuteReader();
            WorkTimeForm.conn.Close();
            if (dr3.Read())
            {
                time_day = Convert.ToString(dr3.GetValue(0));
            }
            WorkTimeForm.conn.Open();
            NpgsqlCommand command2 = new NpgsqlCommand(Global_variables.sql8, WorkTimeForm.conn);
            NpgsqlDataReader dr4 = command2.ExecuteReader();
            WorkTimeForm.conn.Close();
            if (dr4.Read())
            {
                time_month = Convert.ToString(dr4.GetValue(0));
            }
            WorkTimeForm.conn.Open();
            NpgsqlCommand command3 = new NpgsqlCommand(Global_variables.sql9, WorkTimeForm.conn);
            NpgsqlDataReader dr5 = command3.ExecuteReader();
            WorkTimeForm.conn.Close();
            if (dr5.Read())
            {
                time_year = Convert.ToString(dr5.GetValue(0));
            }
            if (time_day.Length == 1) time_day = String.Concat("0", time_day);
            if (time_month.Length == 1) time_month = String.Concat("0", time_month);
            if (time_year.Length == 1) time_year = String.Concat("0", time_year);
            current_date = String.Concat(time_year, "-", time_month, "-", time_day);
            
        }

        public static void variable()
        {



        }
    }
}
